import traceback
from builtins import print as print_builtin
import time
import datetime
# import pymysql
import pandas as pd
from log import log_info


import sys
sys.path.append('..')
import configs.config as config


#连接数据库，读取数据库，取数据内容，需要sql语句
def read_data(sqlSentence)->list:
    db_connect_annotation = pymysql.connect(host=config.db_host, port=config.db_port, user=config.db_user, 
                                        password=config.db_password,db=config.db_database_admin, autocommit=True, 
                                        charset='utf8mb4', connect_timeout=100)
    print("database connection success.")
    log_info.info("database connection success.")
    data_dict = {}
    data_list = []
    sql = ""
    try:
        sql = sqlSentence

        arg_list = []
        cursor = db_connect_annotation.cursor()
        cursor.execute(sql, arg_list)
        cursor_data = cursor.fetchall()
        for i in cursor_data:
            data_dict[i[0]] = {"id":i[0], "addr":i[3], "content":i[5],"infoatname":i[12]}
            text_megre = i[3] + i[5]
            data_list.append({"id":i[0],"text":text_megre,"infoatname":i[12]})
        cursor.close()
    except Exception as e:
        traceback.print_exc()
        print("read mysql database error.")
        log_info.info("read mysql database error.")


    return data_list,data_dict

#读取数据库API
def read_mysql(sql):
    # read_sql_data =  config.read_sql_data

    data_list,data_dict = read_data(sql)
    print("read mysql database success.")
    log_info.info("read mysql database success.")
    return data_list,data_dict

#写入数据库API
#连接数据库，写入数据库，需要sql语句
#全局变量导致数据库连接中断
def write_mysql(data_dict, category_dict):
    db_connect_annotation = pymysql.connect(host=config.db_host, port=config.db_port, user=config.db_user, 
                                        password=config.db_password,db=config.db_database_admin, autocommit=True, 
                                        charset='utf8mb4', connect_timeout=100)
    print("database connection success.")
    log_info.info("database connection success.")
    cursor = db_connect_annotation.cursor()
    # sql = """"INSERT INTO similarOrders (taskid, taskids,infoatname) VALUES (?, ?, ?)", ('asdasd', 222, '1234324')"""
    try:
        # 执行sql语句
        if len(category_dict) != 0:
            for key,category_data_list in category_dict.items():
                ord_id = category_data_list[0]
                category_data = ','.join(category_data_list)
                select_category_data = '|'.join(category_data_list)
                infoatname = data_dict[ord_id]["infoatname"]

                select_sql = "select * from ch_mass_hair_order where taskids regexp %s"
                cursor.execute(select_sql, (str(select_category_data),))
                cursor_data = cursor.fetchall()
                #判断数据库中是否有该类，如果有就update，如果没有就insert
                #print(len(cursor_data))
                if len(cursor_data) == 0:
                    insert_sql = "INSERT INTO ch_mass_hair_order (taskids, infoatname, create_time) VALUES ( %s, %s, now());"
                    cursor.execute(insert_sql, (str(category_data), str(infoatname)))
                else:
                    update_sql = "UPDATE ch_mass_hair_order SET taskids  = %s , update_time  = now() WHERE id = %s;"
                    cursor.execute(update_sql, (str(category_data), str(cursor_data[0][0])))

        # 提交到数据库执行
        db_connect_annotation.commit()
        cursor.close()

        print("write mysql database success.")
        log_info.info("write mysql database success.")
    except:
        # 如果发生错误则回滚
        print("write mysql database error.")
        log_info.info("write mysql database error.")
        db_connect_annotation.rollback()


    return 0


if __name__ == '__main__':
    ids = ["20240118000846","20240118000880"]
    str_data = ",".join(ids)
    sql = f"select * from T_GD2 where position(工单编号 in '{str_data}')"
    
    data_list,data_dict = read_mysql(sql)
    print(len(data_list))